import pymysql  # mysql
import pymssql  # sqlserver
import cx_Oracle  # oracle


class MyDB(object):
    '''连接mysql、sqlserver、oracle等数据库'''

    def __init__(self, classify, host, port, user, password, database, connect=None):
        self.classify = classify.lower().replace(' ', '')
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.connect = connect
        self.set_connect()

    def set_connect(self):
        '''建立数据连接'''
        if self.classify == 'mysql':  # 建立mysql连接
            self.connect = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                           database=self.database)
        elif self.classify == 'sqlserver':  # 建立sqlserver连接
            self.connect = pymssql.connect(self.host, self.user, self.password, self.database)
        elif self.classify == 'oracle':  # 建立oracle连接
            self.connect = cx_Oracle.connect(
                '{user}/{pwd}@{host}:{port}/{database}'.format(user=self.user, pwd=self.password, host=self.host,
                                                               port=self.port,
                                                               database=self.database))
        else:
            self.connect = None
        return self.connect

    def get_field_name(self, sql):
        '''获取查询字段名'''
        # 创建游标
        cur = self.connect.cursor()
        cur.execute(sql)
        field_names = cur.description
        result = [i[0] for i in field_names]
        return result

    def get_dict_datas(self, sql, count=None):
        '''获取字典形式数据'''
        field_names = self.get_field_name(sql)
        cur = self.connect.cursor()
        cur.execute(sql)
        if count:
            datas = cur.fetchmany(count)
        else:
            datas = cur.fetchall()
        # 生成dict数据
        result = [{field_name: data[index] for index, field_name in enumerate(field_names)} for data in datas]
        return result

    def get_list_datas(self, sql, count=None):
        '''获取二维数组形式数据'''
        cur = self.connect.cursor()
        cur.execute(sql)
        if count:
            datas = cur.fetchmany(count)
        else:
            datas = cur.fetchall()
        result = [[i for i in data] for data in datas]
        return result

    def get_all_tables(self):
        '''获取当前连接下所有表名'''
        if self.classify == 'mysql':
            sql = "select table_name from information_schema.tables where table_schema='{database}'".format(
                database=self.database)
        elif self.classify == 'sqlserver':
            sql = "select name from sysobjects where xtype='U'"
        elif self.classify == 'oracle':
            sql = "select TABLE_NAME from all_tables WHERE owner='{owner}'".format(owner=self.user)
        else:
            sql = ''
        cur = self.connect.cursor()
        cur.execute(sql)
        tables = cur.fetchall()
        result = [i[0] for i in tables]
        return result

    def get_all_views(self):
        '''获取当前连接下所有视图'''
        if self.classify == 'mysql':
            sql = "select table_name from information_schema.views where table_schema='{database}'".format(
                database=self.database)
        elif self.classify == 'sqlserver':
            sql = "select name from sysobjects where xtype='V'"
        elif self.classify == 'oracle':
            sql = "select VIEW_NAME from all_views WHERE owner='{owner}'".format(owner=self.user)
        else:
            sql = ''
        cur = self.connect.cursor()
        cur.execute(sql)
        tables = cur.fetchall()
        result = [i[0] for i in tables]
        return result

    def insert(self, sql, data, many=False):
        """
        插入数据:单条()
        多条oracle((),(),)sqlserver:[(),()]
        sql示例：
        oracle:insert into "table_name" ("field1","field2","field3") values ([:1, :2, :3])
        sqlserver:insert into [table_name] ([field1],[field2],[field3]) values (%s,%s,%s)
        mysql:insert into table_name (field1,field2,field3) values (%s,%s,%s)
        """
        cur = self.connect.cursor()
        if many:
            cur.executemany(sql, data)
        else:
            cur.execute(sql, data)

    def update(self, sql, value):
        """修改数据"""
        cur = self.connect.cursor()
        cur.execute(sql, value)

    def delete(self, sql, value):
        """删除数据"""
        cur = self.connect.cursor()
        cur.execute(sql, value)

    def commit(self):
        """提交数据"""
        self.connect.commit()

    def rollback(self):
        """回滚数据"""
        self.connect.rollback()

    def close(self):
        '''关闭数据库连接'''
        self.connect.close()


if __name__ == '__main__':
    pass
    # mysql测试
    # conn = MyDB('mysql', '127.0.0.1', 3306, 'root', '123456', 'test')
    ### 查询
    # field_list = conn.get_field_name("select * from app_cube_echart")
    # print(field_list)
    # datas_dict = conn.get_dict_datas("select * from app_cube_echart")
    # print(datas_dict)
    # datas_list = conn.get_list_datas("select * from app_cube_echart")
    # print(datas_list)
    # tables = conn.get_all_tables()
    # print(tables)
    ### 插入
    # conn.insert("insert into user values(%s, %s, %s)", [(3, "insert1", "123456"), (4, "insert2", "123456")], many=True)
    # conn.commit()
    ### 修改
    # conn.update("update user set password=%s where id=4", ("111111",))
    # conn.commit()
    ### 删除数据
    # conn.delete("delete from user where id=%s", (4,))
    # conn.commit()

    # sqlserver测试
    # conn = MyDB('sqlserver', '127.0.0.1', 1433, 'sa', '123456', 'DaXingTest')
    ### 查询
    # field_list = conn.get_field_name("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    # print(field_list)
    # datas_dict = conn.get_dict_datas("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    # print(datas_dict)
    # datas_list = conn.get_list_datas("select [test1], [test2], [test3], [x_axis] from drawing_test1")
    # print(datas_list)
    # tables = conn.get_all_tables()
    # print(tables)
    ### 插入
    # conn.insert("insert into [user] values(%s, %s, %s)", [(3, "insert1", "123456"), (4, "insert2", "123456")], many=True)
    # conn.commit()
    ### 修改
    # conn.update("update [user] set [password]=%s where id=4", ("111111",))
    # conn.commit()
    ### 删除数据
    # conn.delete("delete from [user] where [id]=%s", (4,))
    # conn.commit()

    # oracle测试
    # conn = MyDB('oracle', '127.0.0.1', 1521, 'C##ZJS', '123456', 'orcl')
    # field_list = conn.get_field_name('select * from "test"')
    # print(field_list)
    # datas_dict = conn.get_dict_datas('select * from "test"', 1)
    # print(datas_dict)
    # datas_list = conn.get_list_datas('select * from "test"', 1)
    # print(datas_list)
    # tables = conn.get_all_tables()
    # print(tables)
    # views = conn.get_all_views()
    # print(views)
    ### 插入
    # conn.insert('insert into "test" values(:1, :2, :3)', [(3, "insert1", "123456"), (4, "insert2", "123456")],
    #             many=True)
    # conn.commit()
    ### 修改
    # conn.update('update "test" set "password"=:v where "id"=:n', {"v": "111111", "n": 4})
    # conn.commit()
    ### 删除数据
    # conn.delete('delete from "test" where "id"=:n', {"n": 4})
    # conn.commit()

    # conn.close()
